-- Descriere:
--
-- Creaza toate entitatile (tabele, secvente, trigere, etc..) necesare 
-- pachetului 'eiga'
--  

DROP TABLE users;
CREATE TABLE users(
  user_id NUMBER(4),
  user_name VARCHAR2(50),
  user_password VARCHAR2(50),
  CONSTRAINT users_user_id_pk PRIMARY KEY (user_id));
  
DROP SEQUENCE seq_user_id;
CREATE SEQUENCE seq_user_id
  START WITH 1 
  INCREMENT BY 1 
  NOMAXVALUE; 

CREATE OR REPLACE TRIGGER trg_user_id
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  SELECT seq_user_id.nextval INTO :new.user_id FROM dual;
END;
/

TRUNCATE TABLE users;
INSERT INTO users (user_id, user_name, user_password)
  VALUES (1, 'radu', 'cevagreu');
  
INSERT INTO users (user_id, user_name, user_password)
  VALUES (2, 'ghita', 'portita');

DROP TABLE movies;
CREATE TABLE movies(
  movie_id NUMBER(4),
  movie_title VARCHAR2(50),
  movie_date NUMBER(4),
  movie_rating NUMBER(2,1),
  movie_director_id NUMBER(4),
  movie_country_id NUMBER(4),
  movie_genre_id NUMBER(4),
  movie_user_id NUMBER(4),
  movie_tag VARCHAR2(500),
  movie_image BLOB,
  CONSTRAINT movies_movie_id_pk PRIMARY KEY(movie_id));

DROP SEQUENCE seq_movie_id;
CREATE SEQUENCE seq_movie_id
  START WITH 1 
  INCREMENT BY 1 
  NOMAXVALUE; 

CREATE OR REPLACE TRIGGER trg_movie_id
BEFORE INSERT ON movies
FOR EACH ROW
BEGIN
  SELECT seq_movie_id.nextval INTO :new.movie_id FROM dual;
END;
/

TRUNCATE TABLE movies;
INSERT INTO movies (movie_title, movie_date, movie_rating, movie_country_id, 
  movie_genre_id, movie_director_id, movie_user_id, movie_tag, movie_image)
  VALUES ('Infernal Affairs', 2002, 8.0, 1, 2, 11, 2, 
  'Loyalty. Honor. Betrayal.', null);

INSERT INTO movies (movie_title, movie_date, movie_rating, movie_country_id, 
  movie_genre_id, movie_director_id, movie_user_id, movie_tag, movie_image)
  VALUES ('Hero', 2002, 8, 2, 5, 12, 2, 
  'Kono kuni wa mada, honto no hero wo shiranai (Japanese)', null);

INSERT INTO movies (movie_title, movie_date, movie_rating, movie_country_id, 
  movie_genre_id, movie_director_id, movie_user_id, movie_tag, movie_image)
  VALUES ('House of Flying Daggers', 2004, 7.4, 1, 1, 12, 2, 'none', null);

INSERT INTO movies (movie_title, movie_date, movie_rating, movie_country_id, 
  movie_genre_id, movie_director_id, movie_user_id, movie_tag, movie_image)
  VALUES ('2046', 2004, 7.5, 1, 2, 13, 2, 
  'Where everything ends... and begins (Spanish)', null);

INSERT INTO movies (movie_title, movie_date, movie_rating, movie_country_id, 
  movie_genre_id, movie_director_id, movie_user_id, movie_tag, movie_image)
  VALUES ('In the mood for love', 2000, 8, 1, 4, 13, 2, 
  'Feel the heat, keep the feeling burning, let the sensation explode. ', null);

INSERT INTO movies (movie_title, movie_date, movie_rating, movie_country_id, 
  movie_genre_id, movie_director_id, movie_user_id, movie_tag, movie_image)
  VALUES ('Ashes of Time', 1994, 7.4, 2, 4, 13, 2, 'none', null);

INSERT INTO movies (movie_title, movie_date, movie_rating, movie_country_id, 
  movie_genre_id, movie_director_id, movie_user_id, movie_tag, movie_image)
  VALUES ('Shark Skin Man and Peach Hip Girl', 1998, 6.9, 3, 1, 14, 2, 'none', 
  null);

INSERT INTO movies (movie_title, movie_date, movie_rating, movie_country_id, 
  movie_genre_id, movie_director_id, movie_user_id, movie_tag, movie_image)
  VALUES ('The Taste of Tea', 2004, 8, 3, 5, 14, 3, 'none', null);

INSERT INTO movies (movie_title, movie_date, movie_rating, movie_country_id, 
  movie_genre_id, movie_director_id, movie_user_id, movie_tag, movie_image)
  VALUES ('Zatoichi', 2003, 7.6, 3, 5, 15, 2, 'none', null);

INSERT INTO movies (movie_title, movie_date, movie_rating, movie_country_id, 
  movie_genre_id, movie_director_id, movie_user_id, movie_tag, movie_image)
  VALUES ('Gohatto', 1999, 6.8, 3, 5, 16, 2, 'none', null);

INSERT INTO movies (movie_title, movie_date, movie_rating, movie_country_id, 
  movie_genre_id, movie_director_id, movie_user_id, movie_tag, movie_image)
  VALUES ('The Fabulous Destiny of Amelie Poulain', 2001, 8.6, 4, 5, 17, 2, 
  'She will change your life.', null);

INSERT INTO movies (movie_title, movie_date, movie_rating, movie_country_id, 
  movie_genre_id, movie_director_id, movie_user_id, movie_tag, movie_image)
  VALUES ('A Very Long Engagement', 2004, 7.8, 4, 5, 17, 2, 'Never let go', 
  null);

INSERT INTO movies (movie_title, movie_date, movie_rating, movie_country_id, 
  movie_genre_id, movie_director_id, movie_user_id, movie_tag, movie_image)
  VALUES ('The City of Lost Children', 1995, 7.8, 4, 5, 17, 2, 
  'Where happily ever after is just a dream.', null);

DROP TABLE people;
CREATE TABLE people(
  person_id NUMBER(4),
  person_name VARCHAR2(60),
  person_function NUMBER(1),
  person_birthday VARCHAR2(60),
  person_country_id NUMBER(4),
  person_image BLOB,
  CONSTRAINT people_person_id_pk PRIMARY KEY(person_id));

DROP SEQUENCE seq_person_id;
CREATE SEQUENCE seq_person_id
  START WITH 1 
  INCREMENT BY 1 
  NOMAXVALUE; 

CREATE OR REPLACE TRIGGER trg_person_id
BEFORE INSERT ON people
FOR EACH ROW
BEGIN
  SELECT seq_person_id.nextval INTO :new.person_id FROM dual;
END;
/

TRUNCATE TABLE people;
INSERT INTO people (person_name, person_function, person_birthday, 
  person_country_id) VALUES ('Tony Leung Chiu Wai', 0, '27-06-1962', 1);

INSERT INTO people (person_name, person_function, person_birthday, 
  person_country_id) VALUES ('Andy Lau', 0, '27-09-1961', 1);

INSERT INTO people (person_name, person_function, person_birthday, 
  person_country_id) VALUES ('Maggie Cheung', 0, '20-09-1964', 1);

INSERT INTO people (person_name, person_function, person_birthday, 
  person_country_id) VALUES ('Ziyi Zhang', 0, '09-02-1979', 2);
  
INSERT INTO people (person_name, person_function, person_birthday, 
  person_country_id) VALUES ('Sie Kohinata', 0, '08-09-1979', 3);

INSERT INTO people (person_name, person_function, person_birthday, 
  person_country_id) VALUES ('Tadanobu Asano', 0, '27-11-1973', 3);

INSERT INTO people (person_name, person_function, person_birthday, 
  person_country_id) VALUES ('Takeshi Kitano', 0, '18-01-1947', 3);

INSERT INTO people (person_name, person_function, person_birthday, 
  person_country_id) VALUES ('Tatsuya Gashuin', 0, '10-12-1950', 3);

INSERT INTO people (person_name, person_function, person_birthday, 
  person_country_id) VALUES ('Audrey Tautou', 0, '09-08-1976', 4);

INSERT INTO people (person_name, person_function, person_birthday, 
  person_country_id) VALUES ('Dominique Pinon', 0, '04-03-1950', 4);

INSERT INTO people (person_name, person_function, person_birthday, 
  person_country_id) VALUES ('Wai Keung Lau', 1, '01-01-1960', 1);

INSERT INTO people (person_name, person_function, person_birthday, 
  person_country_id) VALUES ('Yimou Zhang', 1, '14-11-1951', 2);

INSERT INTO people (person_name, person_function, person_birthday, 
  person_country_id) VALUES ('Kar Wai Wong', 1, '01-01-1971', 2);

INSERT INTO people (person_name, person_function, person_birthday, 
  person_country_id) VALUES ('Katsuhito Ishii', 1, '01-01-1966', 3);

INSERT INTO people (person_name, person_function, person_birthday, 
  person_country_id) VALUES ('Kan Shimozawa', 1, '19-07-1968', 3);

INSERT INTO people (person_name, person_function, person_birthday, 
  person_country_id) VALUES ('Nagisa Oshima', 1, '31-03-1932', 3);

INSERT INTO people (person_name, person_function, person_birthday, 
  person_country_id) VALUES ('Jean-Pierre Jeunet', 1, '03-08-1953', 4);

DROP TABLE movies_actors;
CREATE TABLE movies_actors(
  entry_id NUMBER(4),
  movie_id NUMBER(4),
  actor_id NUMBER(4),
  role VARCHAR(100),
  CONSTRAINT movies_actors_entry_id_pk PRIMARY KEY(entry_id));

DROP SEQUENCE seq_entry_id;
CREATE SEQUENCE seq_entry_id
  START WITH 1 
  INCREMENT BY 1 
  NOMAXVALUE; 

CREATE OR REPLACE TRIGGER trg_entry_id
BEFORE INSERT ON movies_actors
FOR EACH ROW
BEGIN
  SELECT seq_entry_id.nextval INTO :new.entry_id FROM dual;
END;
/

TRUNCATE TABLE movies_actors;
INSERT INTO movies_actors (movie_id, actor_id, role) VALUES (1, 1, 'Chan Wing Yan');
INSERT INTO movies_actors (movie_id, actor_id, role) VALUES (1, 2, 'Inspector Lau Kin Ming');
INSERT INTO movies_actors (movie_id, actor_id, role) VALUES (2, 1, 'Broken Sword');
INSERT INTO movies_actors (movie_id, actor_id, role) VALUES (2, 3, 'Flying Snow');
INSERT INTO movies_actors (movie_id, actor_id, role) VALUES (2, 4, 'Moon');
INSERT INTO movies_actors (movie_id, actor_id, role) VALUES (3, 2, 'Leo');
INSERT INTO movies_actors (movie_id, actor_id, role) VALUES (3, 4, 'Mei');
INSERT INTO movies_actors (movie_id, actor_id, role) VALUES (4, 1, 'Chow Mo Wan');
INSERT INTO movies_actors (movie_id, actor_id, role) VALUES (4, 4, 'Bai Ling');
INSERT INTO movies_actors (movie_id, actor_id, role) VALUES (4, 3, 'Su Li Zhen 1960');
INSERT INTO movies_actors (movie_id, actor_id, role) VALUES (5, 1, 'Chow Mo-wan');
INSERT INTO movies_actors (movie_id, actor_id, role) VALUES (5, 3, 'Su Li-zhen Chan');
INSERT INTO movies_actors (movie_id, actor_id, role) VALUES (6, 1, 'Blind Swordsman');
INSERT INTO movies_actors (movie_id, actor_id, role) VALUES (6, 3, 'The Woman');
INSERT INTO movies_actors (movie_id, actor_id, role) VALUES (7, 5, 'Toshiko Momojiri');
INSERT INTO movies_actors (movie_id, actor_id, role) VALUES (7, 6, 'Kuroo Samehada');
INSERT INTO movies_actors (movie_id, actor_id, role) VALUES (7, 8, 'Yamada');
INSERT INTO movies_actors (movie_id, actor_id, role) VALUES (8, 6, 'Ayano, the Uncle');
INSERT INTO movies_actors (movie_id, actor_id, role) VALUES (8, 8, 'Akira Todoroki');
INSERT INTO movies_actors (movie_id, actor_id, role) VALUES (9, 6, 'Hattori Genosuke');
INSERT INTO movies_actors (movie_id, actor_id, role) VALUES (9, 7, 'Zatoichi/Ichi');
INSERT INTO movies_actors (movie_id, actor_id, role) VALUES (10, 6, 'Samurai Hyozo Tashiro');
INSERT INTO movies_actors (movie_id, actor_id, role) VALUES (10, 7, 'Captain Toshizo Hijikata');
INSERT INTO movies_actors (movie_id, actor_id, role) VALUES (11, 9, 'Amelie Poulain');
INSERT INTO movies_actors (movie_id, actor_id, role) VALUES (11, 10, 'Joseph');
INSERT INTO movies_actors (movie_id, actor_id, role) VALUES (12, 9, 'Mathilde');
INSERT INTO movies_actors (movie_id, actor_id, role) VALUES (12, 10, 'Sylvain');
INSERT INTO movies_actors (movie_id, actor_id, role) VALUES (13, 10, 'le scaphandrier');

DROP TABLE genres;
CREATE TABLE genres(
  genre_id NUMBER(4),
  genre_name VARCHAR2(50),
  genre_description VARCHAR2(300),
  CONSTRAINT genres_genre_id_pk PRIMARY KEY(genre_id));

DROP SEQUENCE seq_genre_id;
CREATE SEQUENCE seq_genre_id
  START WITH 1 
  INCREMENT BY 1 
  NOMAXVALUE; 

CREATE OR REPLACE TRIGGER trg_genre_id
BEFORE INSERT ON genres
FOR EACH ROW
BEGIN
  SELECT seq_genre_id.nextval INTO :new.genre_id FROM dual;
END;
/

TRUNCATE TABLE genres;
INSERT INTO genres (genre_name, genre_description)
  VALUES ('Drama', 'Involves tears');

INSERT INTO genres (genre_name, genre_description)
  VALUES ('Romance', 'Love and its friends');

INSERT INTO genres (genre_name, genre_description)
  VALUES ('Crime', 'A lot of blood');

INSERT INTO genres (genre_name, genre_description)
  VALUES ('Action', 'Fighting and stuff');

INSERT INTO genres (genre_name, genre_description)
  VALUES ('Thriller', 'Makes you wonder');

INSERT INTO genres (genre_name, genre_description)
  VALUES ('Comedy', 'Lought a lot');

INSERT INTO genres (genre_name, genre_description)
  VALUES ('S.F.', 'I see dead people');

DROP TABLE countries;
CREATE TABLE countries(
  country_id NUMBER(4),
  country_name VARCHAR2(60),
  country_description VARCHAR2(100),
  CONSTRAINT countries_country_id_pk PRIMARY KEY(country_id));

DROP SEQUENCE seq_country_id;
CREATE SEQUENCE seq_country_id
  START WITH 1 
  INCREMENT BY 1 
  NOMAXVALUE; 

CREATE OR REPLACE TRIGGER trg_country_id
BEFORE INSERT ON countries
FOR EACH ROW
BEGIN
  SELECT seq_country_id.nextval INTO :new.country_id FROM dual;
END;
/

TRUNCATE TABLE countries;
INSERT INTO countries (country_name, country_description)
  VALUES ('Hong Kong', 'nothing');

INSERT INTO countries (country_name, country_description)
  VALUES ('China', 'nothing');

INSERT INTO countries (country_name, country_description)
  VALUES ('Japan', 'nothing');

INSERT INTO countries (country_name, country_description)
  VALUES ('France', 'nothing');

INSERT INTO countries (country_name, country_description)
  VALUES ('Portugal', 'nothing');
  
INSERT INTO countries (country_name, country_description)
  VALUES ('Thailand', 'nothing');
